﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace OPMS_V1.DOL
{
    public class advertiseDOL
    {
        //This is the connection string for the database located at the localhost
        private const String connString = "server=localhost;database=OPMS;Integrated Security=SSPI";
        
        //Creating the SQLConnection using Connection String
        SqlConnection conn = new SqlConnection(connString);
        SqlDataReader reader = null;

        /// <summary>
        /// This function is used for checking the existance of TaskID
        /// It returns the boolean condition whether the Task ID exists or not
        /// </summary>
        /// <param name="taskId"></param>
        /// <returns></returns>
        public Boolean checkTaskID(String taskId)
        {
            Boolean condition = false;
            //Using the Connection String
            using (SqlCommand command = new SqlCommand("check_task_id", conn))
            {
                try
                {
                    //Open the database Connection
                    conn.Open();
                    //The query type is the stored procedure, much safe than writing Queries
                    command.CommandType = CommandType.StoredProcedure;
                    //Passing in the Parameter
                    command.Parameters.Add("@taskid", SqlDbType.VarChar, 50).Value = taskId;

                    //Execute the Reader
                    reader = command.ExecuteReader();

                    //If the Reader has records then set the condition to true
                    if (reader.HasRows)
                    {
                        condition = true;
                    }
                    else
                    {
                        //condition = false; 
                    }
                }
                //Catch the exception if the sqlConnection is not made.
                catch (SqlException)
                { }
                //Close the connection
                finally
                {
                    //If the connection state is open then close the connection to the database.
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
                return condition;
            }
        }

        /// <summary>
        /// This function is used for checking the existance of PropertyID
        /// It returns the boolean condition whether the PropertyID exists or not
        /// </summary>
        /// <param name="randomProperty"></param>
        /// <returns></returns>
        public Boolean checkPropertyId(String randomProperty)
        {
            Boolean condition = false;
            //Using the Connection String
            using (SqlCommand command = new SqlCommand("check_property_id", conn))
            {
                try
                {
                    //Open the database Connection
                    conn.Open();
                    //The query type is the stored procedure, much safe than writing Queries
                    command.CommandType = CommandType.StoredProcedure;
                    //Passing in the Parameter
                    command.Parameters.Add("@randomProperty", SqlDbType.VarChar, 50).Value = randomProperty;

                    //Execute the Reader
                    reader = command.ExecuteReader();

                    //If the Reader has records then set the condition to true
                    if (reader.HasRows)
                    {
                        condition = true;
                    }
                    else
                    {
                        //condition = false; 
                    }
                }
                //Catch the exception if the sqlConnection is not made.
                catch (SqlException)
                { }
                //Close the connection
                finally
                {
                    //If the connection state is open then close the connection to the database.
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close(); //Connection close
                    }
                }
                return condition;
            }
        }


        /// <summary>
        /// This function is used for checking the existance of LandLordID
        /// It returns the boolean condition whether the LandLordID exists or not
        /// </summary>
        /// <param name="randomLandLordID"></param>
        /// <returns></returns>
        public Boolean checkLandLordID(String randomLandLordID)
        {
            Boolean condition = false;
            //Using the Connection String
            using (SqlCommand command = new SqlCommand("check_LandLord_id", conn))
            {
                try
                {
                    //Open the database Connection
                    conn.Open();
                    //The query type is the stored procedure, much safe than writing Queries
                    command.CommandType = CommandType.StoredProcedure;
                    //Passing in the Parameter
                    command.Parameters.Add("@randomLandlordID", SqlDbType.VarChar, 50).Value = randomLandLordID;

                    //Execute the Reader
                    reader = command.ExecuteReader();

                    //If the Reader has records then set the condition to true
                    if (reader.HasRows)
                    {
                        condition = true;
                    }
                    else
                    {
                        //condition = false; 
                    }
                }
                //Catch the exception if the sqlConnection is not made.
                catch (SqlException)
                { }
                //Close the connection
                finally
                {
                    //If the connection state is open then close the connection to the database.
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close(); //Connection close
                    }
                }
                return condition;
            }
        }


        /// <summary>
        /// This function is used for checking the existance of InventoryID
        /// It returns the boolean condition whether the InventoryID exists or not
        /// </summary>
        /// <param name="InventoryID"></param>
        /// <returns></returns>
        public Boolean checkInventoryID(String InventoryID)
        {
            Boolean condition = false;
            //Using the Connection String
            using (SqlCommand command = new SqlCommand("check_Inventory_id", conn))
            {
                try
                {
                    //Open the database Connection
                    conn.Open();
                    //The query type is the stored procedure, much safe than writing Queries
                    command.CommandType = CommandType.StoredProcedure;
                    //Passing in the Parameter
                    command.Parameters.Add("@InventoryID", SqlDbType.VarChar, 50).Value = InventoryID;

                    //Execute the Reader
                    reader = command.ExecuteReader();

                    //If the Reader has records then set the condition to true
                    if (reader.HasRows)
                    {
                        condition = true;
                    }
                    else
                    {
                        //condition = false; 
                    }
                }
                //Catch the exception if the sqlConnection is not made.
                catch (SqlException)
                { }
                //Close the connection
                finally
                {
                    //If the connection state is open then close the connection to the database.
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close(); //Connection close
                    }
                }
                return condition;
            }
        }

        /// <summary>
        /// This function is used for updating the Landlords personal details.
        /// It updates the information such as FName, LName, sex, dob, phone etc.
        /// </summary>
        /// <param name="fName"></param>
        /// <param name="lName"></param>
        /// <param name="sex"></param>
        /// <param name="dob"></param>
        /// <param name="phone"></param>
        /// <param name="streetnum"></param>
        /// <param name="streetName"></param>
        /// <param name="postCode"></param>
        /// <param name="area"></param>
        /// <param name="state"></param>
        /// <param name="LandLordID"></param>
        /// <returns></returns>
        public DataSet UpdatePDetails(string fName, string lName, string sex, string dob, string phone, string streetnum, string streetName, string postCode, string area, string state, string LandLordID)
        {
            DataSet data = new DataSet();
            //Pssing in the stored procedure into the command
            using (SqlCommand command = new SqlCommand("Update_LandLord_Details", conn))
            {
                try
                {
                    //Open the SQL Connection
                    conn.Open();

                    //Run the command type as a stored procedure
                    command.CommandType = CommandType.StoredProcedure;
                    //Pass on the parameter required by the stored procedure
                    command.Parameters.Add("@fname", SqlDbType.VarChar,50).Value = fName;
                    command.Parameters.Add("@lname", SqlDbType.VarChar,50).Value = lName;
                    command.Parameters.Add("@sex", SqlDbType.VarChar,50).Value = sex;
                    command.Parameters.Add("@dob", SqlDbType.VarChar,50).Value = dob;
                    command.Parameters.Add("@phone", SqlDbType.VarChar,50).Value = phone;
                    command.Parameters.Add("@streetnum", SqlDbType.VarChar,50).Value = streetnum;
                    command.Parameters.Add("@streetname", SqlDbType.VarChar,100).Value = streetName;
                    command.Parameters.Add("@postcode", SqlDbType.VarChar,50).Value = postCode;
                    command.Parameters.Add("@area", SqlDbType.VarChar,100).Value = area;
                    command.Parameters.Add("@state", SqlDbType.VarChar,50).Value = state;
                    command.Parameters.Add("@landlordID", SqlDbType.VarChar,50).Value = LandLordID;
                }
                catch (SqlException)
                {}
                finally
                {
                    //If the connection state is open then close the connection to the database.
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
                return data;
            }
        }

        public DataSet GetStates()
        {
            SqlCommand command = new SqlCommand("getAll_States", conn);
            command.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(command);

            //Filling the DataSet with the Doctor_History Information
            DataSet data = new DataSet();
            da.Fill(data);

            return data;
        }

        public string GetAreaID(string Area, string PostCode, string state)
        {
            string AreaId = null;
            using (SqlCommand command = new SqlCommand("Get_Area_ID", conn))
            {
                try
                {
                    conn.Open();
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@area", SqlDbType.VarChar, 100).Value = Area;
                    command.Parameters.Add("@postalCode", SqlDbType.VarChar, 50).Value = PostCode;
                    command.Parameters.Add("@state", SqlDbType.VarChar, 50).Value = state;

                    reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            AreaId = reader.GetString(0);
                        }
                    }
                }
                catch (SqlException se)
                {
                    string s = se.Message;
                }
                finally
                {
                    //If the connection state is open then close the connection to the database.
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
                return AreaId;

            }
        }

        public int CheckAddress(string Area, string postalCode, string state)
        {
            int condition = 1;

            using (SqlCommand command = new SqlCommand("Get_Selected_Address", conn))
            {
                try
                {
                    conn.Open();

                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@area", SqlDbType.VarChar, 100).Value = Area;
                    command.Parameters.Add("@postalCode", SqlDbType.VarChar, 50).Value = postalCode;
                    command.Parameters.Add("@state", SqlDbType.VarChar, 50).Value = state;

                    reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        condition = 0;
                    }
                    else
                    {
                        condition = 1;
                    }
                }
                catch (SqlException)
                { }
                finally
                {
                    //If the connection state is open then close the connection to the database.
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
                return condition;

            }
        }

        public DataSet Get_PDetails(string accountname)
        {
            DataSet data = new DataSet();
            using (SqlCommand command = new SqlCommand("Get_LandLord_Details", conn))
            {
                try
                {
                    //Open the SQL Connection
                    conn.Open();

                    //Run the command type as a stored procedure
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@loginID", SqlDbType.VarChar, 50).Value = accountname;
                    //Execute the reader for retrieving the results from the database
                    //reader = command.ExecuteReader();

                    //If results forund the read the data
                    SqlDataAdapter da = new SqlDataAdapter(command);
                    //Filling the DataSet with the List of Area Information

                    da.Fill(data);
                }
                catch (SqlException)
                { return null; }
                finally
                {
                    //If the connection state is open then close the connection to the database.
                    if (conn.State == ConnectionState.Open)
                    {//Close the connection
                        conn.Close();
                    }
                }   
            }
            return data;
        }


        public DataSet Get_InventoryID(string PropertyID)
        {
            DataSet data = new DataSet();
            using (SqlCommand command = new SqlCommand("Get_PropertyID", conn))
            {
                try
                {
                    //Open the SQL Connection
                    conn.Open();

                    //Run the command type as a stored procedure
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@PropertyID", SqlDbType.VarChar, 50).Value = PropertyID;
                    //Execute the reader for retrieving the results from the database
                    //reader = command.ExecuteReader();

                    //If results forund the read the data
                    SqlDataAdapter da = new SqlDataAdapter(command);
                    //Filling the DataSet with the List of Area Information

                    da.Fill(data);
                }
                catch (SqlException)
                { return null; }
                finally
                {
                    //If the connection state is open then close the connection to the database.
                    if (conn.State == ConnectionState.Open)
                    {//Close the connection
                        conn.Close();
                    }
                }
            }
            return data;
        }

    }
}
